﻿using ECommon;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;

namespace EOrm.NpgsqlProvider
{
    /// <summary>
    /// postgres数据库的sql构建器
    /// </summary>
    public class PgSqlCreator : SqlCreator
    {
        /// <summary>
        /// 构建选择目标数据表所有记录的sql语句
        /// </summary>
        /// <param name="table">目标数据表</param>
        /// <returns>选择目标数据表所有记录的sql语句</returns>
        public override string Select(ITable table)
        {
            return CreateBaseSelect(table) + ";";
        }
        /// <summary>
        /// 构建按条件选择目标数据表并按条件排序记录的sql语句
        /// </summary>
        /// <typeparam name="T">目标数据表泛型</typeparam>
        /// <param name="table">目标数据表</param>
        /// <param name="whereExp">选择条件表达式</param>
        /// <returns>按条件选择目标数据表并按条件排序记录的sql语句</returns>
        public override string Select<T>(ITable table, Expression<Func<T, bool>>? whereExp)
        {
            string sql = CreateBaseSelect(table);
            PgWhereVisitor visitor = new PgWhereVisitor();
            visitor.Visit(whereExp, table);
            string wherePart = visitor.Combine();
            if (wherePart != null && !string.IsNullOrEmpty(wherePart.Trim()))
            {
                sql= sql+" WHERE "+wherePart;
            }
            sql = sql + ";";
            return sql;
        }
        /// <summary>
        /// 构建按条件选择目标数据表并按条件排序记录的sql语句
        /// </summary>
        /// <typeparam name="T">目标数据表泛型</typeparam>
        /// <typeparam name="U">目标数据字段</typeparam>
        /// <param name="table">目标数据表</param>
        /// <param name="whereExp">选择条件表达式</param>
        /// <param name="orderByExp">排序表达式</param>
        /// <returns>按条件选择目标数据表并按条件排序记录的sql语句</returns>
        public override string Select<T, U>(ITable table, Expression<Func<T, bool>> whereExp, Expression<Func<T, U>> orderByExp)
        {
            string sql = CreateBaseSelect(table);
            PgWhereVisitor whereVisitor = new PgWhereVisitor();
            whereVisitor.Visit(whereExp, table);
            string wherePart = whereVisitor.Combine();
            PgOrderByVisitor orderByVisitor = new PgOrderByVisitor();
            orderByVisitor.Visit(orderByExp, table);
            string orderByPart = orderByVisitor.Combine();
            if (wherePart != null && !string.IsNullOrEmpty(wherePart.Trim()))
            {
                sql = sql + " WHERE " + wherePart;
            }
            if (orderByPart != null && !string.IsNullOrEmpty(orderByPart.Trim()))
            {
                sql = sql + " ORDER BY " + orderByPart;
            }
            sql = sql + ";";
            return sql;
        }

        /// <summary>
        /// 构建按条件选择目标数据表并按条件排序记录的sql语句
        /// </summary>
        /// <typeparam name="T">目标数据表泛型</typeparam>
        /// <typeparam name="U">目标数据字段</typeparam>
        /// <param name="table">目标数据表</param>
        /// <param name="whereExp">选择条件表达式</param>
        /// <param name="orderByExp">排序表达式</param>
        /// <returns>按条件选择目标数据表并按条件排序记录的sql语句</returns>
        public override string Select<T, U>(ITable table, Expression<Func<T, bool>>? whereExp, Expression<Func<T, U>>? orderByExp, int limit) where T : class
        {
            string sql = CreateBaseSelect(table);
            PgWhereVisitor whereVisitor = new PgWhereVisitor();
            whereVisitor.Visit(whereExp, table);
            string wherePart = whereVisitor.Combine();
            PgOrderByVisitor orderByVisitor = new PgOrderByVisitor();
            orderByVisitor.Visit(orderByExp, table);
            string orderByPart = orderByVisitor.Combine();
            if (wherePart != null && !string.IsNullOrEmpty(wherePart.Trim()))
            {
                sql = sql + " WHERE " + wherePart;
            }
            if (orderByPart != null && !string.IsNullOrEmpty(orderByPart.Trim()))
            {
                sql = sql + " ORDER BY " + orderByPart;
            }
            if (limit>0)
            {
                sql=sql+" LIMIT "+limit.ToString();
            }
            sql = sql + ";";
            return sql;
        }

        /// <summary>
        /// 构建删除对应单一实体的sql语句
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="table">目标数据表</param>
        /// <param name="entity">要删除的实体</param>
        /// <returns>删除对应单一实体的sql语句</returns>
        public override string DeleteOne<T>(ITable table, T? entity) where T : class
        {
            string sql = CreateBaseDelete(table);
            string wherePart = CreateConditionByEntity(table, entity);
            if (wherePart != null && !string.IsNullOrEmpty(wherePart.Trim()))
            {
                sql = sql + " WHERE " + wherePart;
            }
            sql=sql+";";
            return sql;
        }

        /// <summary>
        /// 构建删除对应列表实体的sql语句
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="table">目标数据表</param>
        /// <param name="entities">要删除的实体列表</param>
        /// <returns>删除对应列表实体的sql语句</returns>
        public override string DeleteList<T>(ITable table, List<T>? entities) where T : class
        {
            string sql = CreateBaseDelete(table);
            if (entities != null)
            {
                List<string> whereParts = entities.Select(a => CreateConditionByEntity(table, a)).ToList();
                string wherePart = string.Join(" OR ", whereParts.ToArray());
                if (wherePart != null && !string.IsNullOrEmpty(wherePart.Trim()))
                {
                    sql = sql + " WHERE " + wherePart;
                }
            }
            sql=sql+";";
            return sql;
        }

        /// <summary>
        /// 构建按条件删除目标记录的sql语句
        /// </summary>
        /// <typeparam name="T">目标数据表泛型</typeparam>
        /// <param name="table">目标数据表</param>
        /// <param name="whereExp">选择条件表达式</param>
        /// <returns>按条件删除目标记录的sql语句</returns>
        public override string Delete<T>(ITable table, Expression<Func<T, bool>>? whereExp)
        {
            string sql = CreateBaseSelect(table);
            PgWhereVisitor whereVisitor = new PgWhereVisitor();
            whereVisitor.Visit(whereExp, table);
            string wherePart = whereVisitor.Combine();
            if (wherePart != null && !string.IsNullOrEmpty(wherePart.Trim()))
            {
                sql = sql + " WHERE " + wherePart;
            }
            sql=sql+";";
            return sql;
        }

        /// <summary>
        /// 构建修改对应实体的sql语句
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="table">目标数据表</param>
        /// <param name="entity">要修改的实体</param>
        /// <returns>修改对应实体的sql语句</returns>
        public override string Update<T>(ITable table, T? entity) where T : class
        {
            string sql = CreateBaseUpdate(table);
            string updateMain = CreateUpdate(table, entity);
            string wherePart = CreateConditionByEntity(table, entity);
            sql=sql+" SET "+updateMain;
            if (wherePart != null && !string.IsNullOrEmpty(wherePart.Trim()))
            {
                sql = sql + " WHERE " + wherePart;
            }
            sql=sql+";";
            return sql;
        }

        /// <summary>
        /// 构建添加对应实体的sql语句
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="table">目标数据表</param>
        /// <param name="entity">要添加的实体</param>
        /// <returns>添加对应实体的sql语句</returns>
        public override string Insert<T>(ITable table, T? entity) where T : class
        {
            string sql = CreateBaseInsert(table);
            string valPart = "";
            string fieldPart = CreateInsert(table, entity, out valPart);
            sql=sql+" ("+fieldPart+ ") VALUES (" + valPart+");";
            return sql;
        }

        /// <summary>
        /// 构建添加对应实体并返回主键的sql语句
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="table">目标数据表</param>
        /// <param name="entity">要添加的实体</param>
        /// <returns>添加对应实体并返回主键的sql语句</returns>
        public override string InsertReturnKey<T>(ITable table, T? entity) where T : class
        {
            string sql = CreateBaseInsert(table);
            string valPart = "";
            string fieldPart = CreateInsert(table, entity, out valPart);
            string returnPart = CreateInsertReturnKey(table, entity);
            sql=sql+" ("+fieldPart+ ") VALUES (" + valPart+") "+returnPart+";";
            return sql;
        }

        /// <summary>
        /// 构建按条件统计记录的sql语句
        /// </summary>
        /// <typeparam name="T">目标数据表泛型</typeparam>
        /// <param name="table">目标数据表</param>
        /// <param name="whereExp">选择条件表达式</param>
        /// <returns>按条件统计记录的sql语句</returns>
        public override string Count<T>(ITable table, Expression<Func<T, bool>>? whereExp)
        {
            string sql = CreateBaseCount(table);
            PgWhereVisitor visitor = new PgWhereVisitor();
            visitor.Visit(whereExp, table);
            string wherePart = visitor.Combine();
            if (wherePart != null && !string.IsNullOrEmpty(wherePart.Trim()))
            {
                sql= sql+" WHERE "+wherePart;
            }
            sql = sql + ";";
            return sql;
        }

        /// <summary>
        /// 构建分页sql语句
        /// </summary>
        /// <typeparam name="T">目标数据表泛型</typeparam>
        /// <typeparam name="U">目标排序字段泛型</typeparam>
        /// <param name="table">数据表</param>
        /// <param name="whereExp">选择条件表达式</param>
        /// <param name="orderByExp">排序条件表达式</param>
        /// <param name="limit">行数限制</param>
        /// <param name="offset">跃进行数</param>
        /// <returns>分页sql语句</returns>
        public override string Page<T, U>(ITable table, Expression<Func<T, bool>>? whereExp = null, Expression<Func<T, U>>? orderByExp = null, int limit = 10, int offset = 0)
        {
            string sql = CreateBaseSelect(table);
            PgWhereVisitor whereVisitor = new PgWhereVisitor();
            whereVisitor.Visit(whereExp, table);
            string wherePart = whereVisitor.Combine();
            PgOrderByVisitor orderByVisitor = new PgOrderByVisitor();
            orderByVisitor.Visit(orderByExp, table);
            string orderByPart = orderByVisitor.Combine();
            if (wherePart != null && !string.IsNullOrEmpty(wherePart.Trim()))
            {
                sql = sql + " WHERE " + wherePart;
            }
            if (orderByPart != null && !string.IsNullOrEmpty(orderByPart.Trim()))
            {
                sql = sql + " ORDER BY " + orderByPart;
            }
            if (limit>0)
            {
                sql=sql+" LIMIT "+limit.ToString();
            }
            if (offset>0)
            {
                sql=sql+" OFFSET "+offset.ToString();
            }
            sql = sql + ";";
            return sql;
        }
        //================================================
        /// <summary>
        /// 构建数据表的基础统计语句
        /// </summary>
        /// <param name="table">目标数据表</param>
        /// <returns>基础统计语句</returns>
        /// <exception cref="ArgumentNullException">参数为空异常</exception>
        /// <exception cref="ArgumentException">变量处理异常</exception>
        public string CreateBaseCount(ITable table)
        {
            if (table == null)
            {
                throw new ArgumentNullException("table", "构建基础查询语句时目标数据表为空");
            }
            if (table.DbName == null || string.IsNullOrEmpty(table.DbName.Trim()))
            {
                throw new ArgumentException("目标数据表名为空", "table.DbName");
            }
            string baseSql = string.Format("SELECT COUNT(*) FROM {0}", table.DbName);
            return baseSql;
        }
        /// <summary>
        /// 构建数据表的基础查询语句
        /// </summary>
        /// <param name="table">目标数据表</param>
        /// <returns>基础查询语句</returns>
        /// <exception cref="ArgumentNullException">参数为空异常</exception>
        /// <exception cref="ArgumentException">变量处理异常</exception>
        public string CreateBaseSelect(ITable table)
        {
            if (table == null)
            {
                throw new ArgumentNullException("table", "构建基础查询语句时目标数据表为空");
            }
            if (table.DbName == null || string.IsNullOrEmpty(table.DbName.Trim()))
            {
                throw new ArgumentException("目标数据表名为空", "table.DbName");
            }
            string baseSql = string.Format("SELECT * FROM {0}", table.DbName);
            return baseSql;
        }

        /// <summary>
        /// 构建数据表的基础删除语句
        /// </summary>
        /// <param name="table">目标数据表</param>
        /// <returns>基础删除语句</returns>
        /// <exception cref="ArgumentNullException">参数为空异常</exception>
        /// <exception cref="ArgumentException">变量处理异常</exception>
        public string CreateBaseDelete(ITable table)
        {
            if (table == null)
            {
                throw new ArgumentNullException("table", "构建基础查询语句时目标数据表为空");
            }
            if (table.DbName == null || string.IsNullOrEmpty(table.DbName.Trim()))
            {
                throw new ArgumentException("目标数据表名为空", "table.DbName");
            }
            string baseSql = string.Format("DELETE FROM {0}", table.DbName);
            return baseSql;
        }

        /// <summary>
        /// 构建数据表的基础更新语句
        /// </summary>
        /// <param name="table">目标数据表</param>
        /// <returns>基础更新语句</returns>
        /// <exception cref="ArgumentNullException">参数为空异常</exception>
        /// <exception cref="ArgumentException">变量处理异常</exception>
        public string CreateBaseUpdate(ITable table)
        {
            if (table == null)
            {
                throw new ArgumentNullException("table", "构建基础查询语句时目标数据表为空");
            }
            if (table.DbName == null || string.IsNullOrEmpty(table.DbName.Trim()))
            {
                throw new ArgumentException("目标数据表名为空", "table.DbName");
            }
            string baseSql = string.Format("UPDATE {0}", table.DbName);
            return baseSql;
        }

        /// <summary>
        /// 构建数据表的基础添加语句
        /// </summary>
        /// <param name="table">目标数据表</param>
        /// <returns>基础添加语句</returns>
        /// <exception cref="ArgumentNullException">参数为空异常</exception>
        /// <exception cref="ArgumentException">变量处理异常</exception>
        public string CreateBaseInsert(ITable table)
        {
            if (table == null)
            {
                throw new ArgumentNullException("table", "构建基础查询语句时目标数据表为空");
            }
            if (table.DbName == null || string.IsNullOrEmpty(table.DbName.Trim()))
            {
                throw new ArgumentException("目标数据表名为空", "table.DbName");
            }
            string baseSql = string.Format("INSERT INTO {0}", table.DbName);
            return baseSql;
        }

        /// <summary>
        /// 构建目标数据表的删除条件
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="table">目标数据表</param>
        /// <param name="entity">目标实体</param>
        /// <returns>删除条件</returns>
        /// <exception cref="ArgumentNullException">变量为空异常</exception>
        public string CreateConditionByEntity<T>(ITable table, T? entity) where T : class
        {
            if (table == null)
            {
                throw new ArgumentNullException("table", "构建基础删除语句时目标数据表为空");
            }
            if (table.Fields==null||table.Fields.Count<=0)
            {
                throw new ArgumentNullException("table.Fields", "构建基础删除语句时目标数据表的字段为空");
            }
            List<IField> fields = table.Fields.FindAll(a => a.IsKey).ToList();
            if (fields.Count<=0)
            {
                fields=table.Fields;
            }
            StringBuilder sb = new StringBuilder();
            foreach (IField field in fields)
            {
                if (entity==null)
                {
                    continue;
                }
                PropertyInfo pi = ReflectionTool.GetPropertyInfo(entity, field.Name);
                object? val = pi.GetValue(entity, null);
                sb.Append(field.DbName);
                sb.Append(" ");
                sb.Append(WrapConditionVal(val, field));
            }
            return "("+sb.ToString()+")";
        }

        /// <summary>
        /// 构建目标数据表的更新主体子串
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="table">目标数据表</param>
        /// <param name="entity">目标实体</param>
        /// <returns>更新主体子串</returns>
        /// <exception cref="ArgumentException">变量为空异常</exception>
        private string CreateUpdate<T>(ITable table, T? entity) where T : class
        {

            List<IField> fields = table.Fields.FindAll(a => !a.IsKey).ToList();
            if (fields.Count<=0)
            {
                throw new ArgumentException("没有可供更新的字段", "fields");
            }
            List<string> whereParts = new List<string>();
            foreach (IField field in fields)
            {
                if (entity==null)
                {
                    continue;
                }
                PropertyInfo pi = ReflectionTool.GetPropertyInfo(entity, field.Name);
                object? val = pi.GetValue(entity, null);
                whereParts.Add(string.Format("{0} {1}", field.DbName, WrapUpdateVal(val, field)));
            }
            return string.Join(",", whereParts.ToArray());
        }

        /// <summary>
        /// 构建目标数据表的添加主体子串
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="table">目标数据表</param>
        /// <param name="entity">目标实体</param>
        /// <param name="valPart">目标实体要插入的值的部分</param>
        /// <returns>更新主体子串</returns>
        /// <exception cref="ArgumentException">变量为空异常</exception>
        private string CreateInsert<T>(ITable table, T? entity, out string valPart) where T : class
        {
            List<IField> fields = table.Fields.FindAll(a => !a.IsAutoFill).ToList();
            if (fields.Count<=0)
            {
                throw new ArgumentException("没有可供添加的字段", "fields");
            }
            List<string> fieldList = fields.Select(a => a.DbName).ToList();
            List<string> valList = new List<string>();
            foreach (IField field in fields)
            {
                if (entity==null)
                {
                    continue;
                }
                PropertyInfo pi = ReflectionTool.GetPropertyInfo(entity, field.Name);
                object? val = pi.GetValue(entity, null);
                valList.Add(WrapInsertVal(val, field));
            }
            valPart=string.Join(",", valList.ToArray());
            return string.Join(",", fieldList.ToArray());
        }

        /// <summary>
        /// 构建目标数据表的添加主键返回部分
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="table">目标数据表</param>
        /// <param name="entity">目标实体</param>
        /// <returns>目标数据表的添加主键返回部分</returns>
        /// <exception cref="ArgumentException">变量为空异常</exception>
        private string CreateInsertReturnKey<T>(ITable table, T? entity) where T : class
        {
            List<IField> fields = table.Fields.FindAll(a => a.IsKey).ToList();
            if (fields==null||fields.Count!=1)
            {
                throw new ArgumentException("非独立主键不可以直接返回", "fields");
            }

            return "RETURNING "+fields[0].DbName;
        }
        /// <summary>
        /// 包装字段的查询条件值
        /// </summary>
        /// <param name="val">源值</param>
        /// <param name="field">字段</param>
        /// <returns>包装后的值</returns>
        /// <exception cref="ArgumentException">未查询到的包装项目异常</exception>
        private string WrapConditionVal(object? val, IField field)
        {
            switch (field.DbTypeName)
            {
                case "samllint":
                case "integer":
                case "bigint":
                case "real":
                case "double":
                case "boolean":
                    return val==null ? "IS NULL" : "= "+ConvertTool.ToString(val);
                case "varchar":
                case "timestamp without timezone":
                    return val==null ? "IS NULL" : "= <%"+ConvertTool.ToString(val)+"%>";
                default:
                    throw new ArgumentException(string.Format("变量类型{0}在postgres数据库中无法进行包装", field.DbTypeName));
            }
        }

        /// <summary>
        /// 包装字段的更新值
        /// </summary>
        /// <param name="val">源值</param>
        /// <param name="field">字段</param>
        /// <returns>包装后的值</returns>
        /// <exception cref="ArgumentException">未查询到的包装项目异常</exception>
        private string WrapUpdateVal(object? val, IField field)
        {
            switch (field.DbTypeName)
            {
                case "samllint":
                case "integer":
                case "bigint":
                case "real":
                case "double":
                case "boolean":
                    return val==null ? "= NULL" : "= "+ConvertTool.ToString(val);
                case "varchar":
                case "timestamp without timezone":
                    return val==null ? "= NULL" : "= <%"+ConvertTool.ToString(val)+"%>";
                default:
                    throw new ArgumentException(string.Format("变量类型{0}在postgres数据库中无法进行包装", field.DbTypeName));
            }
        }

        /// <summary>
        /// 包装字段的插入值
        /// </summary>
        /// <param name="val">源值</param>
        /// <param name="field">字段</param>
        /// <returns>包装后的值</returns>
        /// <exception cref="ArgumentException">未查询到的包装项目异常</exception>
        private string WrapInsertVal(object? val, IField field)
        {
            switch (field.DbTypeName)
            {
                case "samllint":
                case "integer":
                case "bigint":
                case "real":
                case "double":
                case "boolean":
                    if (val == null)
                    { 
                        return "NULL";
                    }
                    string? tmp = ConvertTool.ToString(val);
                    return tmp==null ? "NULL" : tmp;
                case "varchar":
                case "timestamp without timezone":
                    return val==null ? "NULL" : "<%"+ConvertTool.ToString(val)+"%>";
                default:
                    throw new ArgumentException(string.Format("变量类型{0}在postgres数据库中无法进行包装", field.DbTypeName));
            }
        }
    }
}
